import sqlite3
import sys
import time as timepkg

class dbase:
    REGULAR_MODE = 1
    AD_MODE = 2
    
    #"table names"
    UserMode_TABLE = "usermode"
    RegTimeStamp_TABLE = "reg_timestamp"
    AdTimeStamp_TABLE = "ad_timestamp"
    RegStack_TABLE = "reg_stack"
    AdStack_TABLE = "ad_stack"
    UserHits_TABLE = "user_hits"
    UserPackets_TABLE = "user_packets"
    
    #key names
    user_FIELD = "user"
    mode_FIELD = "mode"
    time_FIELD = "time"
    filename_FIELD = "filename"
    hits_FIELD = "hits"
    
    "instantiates DAO layer with fileName as db"
    def __init__(self, fileName='usersDB.sq3'):
        # create/open database file
        self.m_Connection = sqlite3.connect(fileName)
        self.m_Connection.row_factory = sqlite3.Row
        self.m_Row = None
        
        self.m_Cursor = self.m_Connection.cursor()
        
        #create USER table if it doesn't exist'
        #self.m_Cursor.execute(self.scriptCreateTable())
        self.CreateTables()
           
        self.m_Connection.isolation_level = None
        self.m_Connection.commit();
        
    def CreateTables(self):
        self.m_Cursor.execute(self.scriptCreateUserMode())
        self.m_Cursor.execute(self.scriptCreateUserHits())
        self.m_Cursor.execute(self.scriptCreateRegTimestamp())
        self.m_Cursor.execute(self.scriptCreateAdTimestamp())
        self.m_Cursor.execute(self.scriptCreateRegStack())
        self.m_Cursor.execute(self.scriptCreateAdStack())
        self.m_Cursor.execute(self.scriptCreatePacketRequestTime())
    
    def userExists(self, user):
        found = False
        self.m_Cursor.execute(self.userExistsQuery(user))
        self.m_Row = self.m_Cursor.fetchone()
        found = True if (self.m_Row is not None) else False
        
        return found
    
    def insertUserTime(self, user, time, mode):
        self.m_Cursor.execute(self.insertUserTimeQuery(user, time, mode))
    
    def push_Regular(self, user, fileName):
        self.m_Cursor.execute(self.push_FileQuery(user, fileName, dbase.REGULAR_MODE))
    
    def pop_Regular(self, user):
        fileName = self.Pop_File(user, dbase.REGULAR_MODE)
        
        if fileName is None:
            sys.stderr.write("pop_regular() :: No file returned/popped for " + user)
        
        return fileName
    
    def pop_Ad(self, user):
        fileName = self.Pop_File(user, dbase.AD_MODE)
        
        if fileName is None:
            #sys.stderr.write("pop_Ad() :: No file returned/popped for " + user)
            pass
        
        return fileName
    
    def push_Ad(self, user, adName):
        self.m_Cursor.execute(self.push_FileQuery(user, adName, dbase.AD_MODE))
    
    def getLastAccessedTime(self, user, mode):
        lastAccessTime = None
        tableName = self.ResolveTable(mode, dbase.RegTimeStamp_TABLE, dbase.AdTimeStamp_TABLE)
        
        # SELECT MAX(time) FROM REG_TS WHERE user= USER
        self.m_Cursor.execute("SELECT MAX("+dbase.time_FIELD+") FROM " + tableName + "\
        WHERE " + dbase.user_FIELD + "= ?", [user])
        self.m_Row = self.m_Cursor.fetchone()
        
        lastAccessTime = self.m_Row[0]
        
        if lastAccessTime is None:
            sys.stderr.write("getLastAccessedTime: Error Retrieving LastAccessedTime for " + user + " " + mode)
        
        return lastAccessTime
    
    def getUserMode(self, user):
        mode = None
        
        #SELECT MODE FROM table where user = user
        self.m_Cursor.execute("SELECT "+dbase.mode_FIELD + " FROM "+dbase.UserMode_TABLE + " \
        WHERE " + dbase.user_FIELD + "=?",[user])
        self.m_Row = self.m_Cursor.fetchone()
        
        mode = self.m_Row[dbase.mode_FIELD]
        
        if mode is None:
            sys.stderr.write("getUserMode: Failed to retrieve mode for " + user)
        
        return mode
    
    def setUserMode(self, user, mode):
        if self.userExists(user) is False:
            #insert into table(user, mode) values ('pj',AD)
            self.m_Cursor.execute("INSERT INTO " + dbase.UserMode_TABLE + "\
            (" + dbase.user_FIELD + ", " + dbase.mode_FIELD +") \
            VALUES (?, ?)", [user, mode])
        else:
            # update tableName set mode = newMode where user = userName
            self.m_Cursor.execute("UPDATE " + dbase.UserMode_TABLE + " SET \
            " + dbase.mode_FIELD + " = ? WHERE " + dbase.user_FIELD + " = ?", [mode, user])
    
    def clearAdStack(self, user):
        #DELETE FROM ad_ts WHERE userName = user
        self.m_Cursor.execute("DELETE FROM " + dbase.AdStack_TABLE + " WHERE " + dbase.user_FIELD + "=?",[user])

    def ResolveTable(self, mode, regularTable, adTable):
        tableName = None
        if mode == dbase.REGULAR_MODE:
            tableName = regularTable
        elif mode == dbase.AD_MODE:
            tableName = adTable
            
        if tableName is None:
            sys.stderr.write("dbase :: insertUserTimeQuery() :: Invalid Mode: " + mode)
        
        return tableName
    
    def userExistsQuery(self, user):
        return  ("SELECT " + dbase.user_FIELD + "\
        FROM " + dbase.UserMode_TABLE + " \
        WHERE " + dbase.user_FIELD + " = \"%s\" "% (user))
    
    "if first time user: insert ELSE update"
    def insertUserTimeQuery(self, user, time, mode):
        
        tableName = self.ResolveTable(mode, dbase.RegTimeStamp_TABLE, dbase.AdTimeStamp_TABLE)
            
        if self.userExists(user) is False:
            return ("INSERT INTO " + tableName + " \
            (" + dbase.user_FIELD + ", " + dbase.time_FIELD + ")\
            VALUES (\"%s\", \"%s\")" % (user, time))
        else:
            query = "UPDATE " + tableName + " \
             SET " + dbase.time_FIELD + " = \"%s\" " % (time) + " \
             WHERE " + dbase.user_FIELD + " = \"%s\" " % (user)
            return (query)

    def push_FileQuery(self, user, fileName, mode):
        tableName = self.ResolveTable(mode, dbase.RegStack_TABLE, dbase.AdStack_TABLE)
        
        return ("INSERT INTO " + tableName + "\
        (" + dbase.user_FIELD + ", " + dbase.filename_FIELD + ", " + dbase.time_FIELD + ")\
        VALUES (\"%s\",\"%s\",\"%s\")" % (user, fileName, timepkg.time()))
    
    def Pop_File(self, user, mode):
        fileValue = None
        
        tableName = self.ResolveTable(mode, dbase.RegStack_TABLE, dbase.AdStack_TABLE)
        
        self.m_Cursor.execute("SELECT MAX("+dbase.time_FIELD+"), " + dbase.filename_FIELD +"\
         from " + tableName + " WHERE " + dbase.user_FIELD +"= ?",[user])
        self.m_Row = self.m_Cursor.fetchone()
        fileValue = self.m_Row[dbase.filename_FIELD]
        
        if fileValue is None:
            pass
            #sys.stderr.write("Pop_File FAILED to retrieve for "+ user + " in mode\n")
        else:
            self.m_Cursor.execute("DELETE FROM " + tableName + "\
             WHERE " + dbase.user_FIELD + " = ? AND " + dbase.time_FIELD + " = ?", [user, self.m_Row["MAX("+dbase.time_FIELD+")"]])   #row[0] : MAX(time)
        
        return fileValue
        
    "Creates a table which stores user and the mode they are in"
    def scriptCreateUserMode(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.UserMode_TABLE + " ( \
         " + dbase.user_FIELD + " TEXT PRIMARY KEY, \
         " + dbase.mode_FIELD + " TEXT NOT NULL);"
    
    def scriptCreateUserHits(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.UserHits_TABLE + " ( \
         " + dbase.user_FIELD + " TEXT PRIMARY KEY, \
         " + dbase.hits_FIELD + " TEXT NOT NULL);"
    
    def scriptCreatePacketRequestTime(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.UserPackets_TABLE + " ( \
         " + dbase.user_FIELD + " TEXT PRIMARY KEY, \
         " + dbase.time_FIELD + " TEXT NOT NULL);"
         
    def scriptCreateRegTimestamp(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.RegTimeStamp_TABLE + " ( \
         " + dbase.user_FIELD + " TEXT PRIMARY KEY, \
         " + dbase.time_FIELD + " TEXT NOT NULL);"
         
    def scriptCreateAdTimestamp(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.AdTimeStamp_TABLE + " ( \
         " + dbase.user_FIELD + " TEXT PRIMARY KEY, \
         " + dbase.time_FIELD + " TEXT NOT NULL);"
         
    def scriptCreateRegStack(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.RegStack_TABLE + " ( \
           id INTEGER NOT NULL, \
         " + dbase.user_FIELD + " TEXT NOT NULL, \
         " + dbase.filename_FIELD + " TEXT NOT NULL, \
         " + dbase.time_FIELD + " TEXT NOT NULL, \
         PRIMARY KEY(id), \
         UNIQUE (" + dbase.user_FIELD +", " + dbase.filename_FIELD + ") \
         );"
         
    def scriptCreateAdStack(self):
        return "CREATE TABLE IF NOT EXISTS " + dbase.AdStack_TABLE + " ( \
           id INTEGER NOT NULL, \
         " + dbase.user_FIELD + " TEXT NOT NULL, \
         " + dbase.filename_FIELD + " TEXT NOT NULL, \
         " + dbase.time_FIELD + " TEXT NOT NULL, \
         PRIMARY KEY(id), \
         UNIQUE (" + dbase.user_FIELD +", " + dbase.filename_FIELD + ") \
         );"

    def DeleteUser(self, user):
        # DELETE FROM usermode where user='pj'
        self.m_Cursor.execute("DELETE FROM " + dbase.UserMode_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.RegTimeStamp_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.AdTimeStamp_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.RegStack_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.AdStack_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.UserHits_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        self.m_Cursor.execute("DELETE FROM " + dbase.UserPackets_TABLE + " WHERE " + dbase.user_FIELD + " = '%s'" % user)
        
    def updateHits(self, user, hitCount = 0):
        userFound = False
        self.m_Cursor.execute("SELECT " + dbase.user_FIELD + "\
        FROM " + dbase.UserHits_TABLE + " \
        WHERE " + dbase.user_FIELD + " = \"%s\" "% (user))
        self.m_Row = self.m_Cursor.fetchone()
        
        userFound = True if (self.m_Row is not None) else False
        
        if userFound is False:
            self.m_Cursor.execute("INSERT INTO " + dbase.UserHits_TABLE + " \
            (" + dbase.user_FIELD + ", " + dbase.hits_FIELD + ")\
            VALUES (\"%s\", \"%s\")" % (user, 0 ))
        elif hitCount == -2:    #autoincrement hitCount
            # retrieve hits from table
            # add 1 and update table with that and return that
            self.m_Cursor.execute("SELECT "+dbase.hits_FIELD+" FROM " + dbase.UserHits_TABLE + "\
            WHERE " + dbase.user_FIELD + "= ?", [user])
            self.m_Row = self.m_Cursor.fetchone()
            
            hits = self.m_Row[0]
            hits = int(hits) + 1
            
            self.m_Cursor.execute("UPDATE " + dbase.UserHits_TABLE + " \
             SET " + dbase.hits_FIELD + " = \"%s\" " % (str(hits)) + " \
             WHERE " + dbase.user_FIELD + " = \"%s\" " % (user))
        else:
            self.m_Cursor.execute("UPDATE " + dbase.UserHits_TABLE + " \
             SET " + dbase.hits_FIELD + " = \"%s\" " % (str(hitCount)) + " \
             WHERE " + dbase.user_FIELD + " = \"%s\" " % (user))
            
    def GetHits(self, user):
        self.m_Cursor.execute("SELECT "+dbase.hits_FIELD+" FROM " + dbase.UserHits_TABLE + "\
        WHERE " + dbase.user_FIELD + "= ?", [user])
        self.m_Row = self.m_Cursor.fetchone()
        
        hits = self.m_Row[0]
        if hits is None:
            return 0
        return hits

    def GetLastRequestTime(self, user):
        self.m_Cursor.execute("SELECT "+dbase.time_FIELD+" FROM " + dbase.UserPackets_TABLE + "\
        WHERE " + dbase.user_FIELD + "= ?", [user])
        self.m_Row = self.m_Cursor.fetchone()
        
        if self.m_Row is None:
            return 0
        
        packetTime = self.m_Row[0]
        return float(packetTime)
    
    def UpdateRequestTime(self, user, requestTime):
        userFound = False
        self.m_Cursor.execute("SELECT " + dbase.user_FIELD + "\
        FROM " + dbase.UserPackets_TABLE + " \
        WHERE " + dbase.user_FIELD + " = \"%s\" "% (user))
        self.m_Row = self.m_Cursor.fetchone()
        
        userFound = True if (self.m_Row is not None) else False
        
        if userFound is False:
            self.m_Cursor.execute("INSERT INTO " + dbase.UserPackets_TABLE + " \
            (" + dbase.user_FIELD + ", " + dbase.time_FIELD + ")\
            VALUES (\"%s\", \"%s\")" % (user, timepkg.time() ))
        else:
            self.m_Cursor.execute("UPDATE " + dbase.UserPackets_TABLE + " \
             SET " + dbase.time_FIELD + " = \"%s\" " % (str(requestTime)) + " \
             WHERE " + dbase.user_FIELD + " = \"%s\" " % (user))

"""         
dao = dbase("temp.db")
user = "pj"
time = timepkg.time()
mode = dbase.REGULAR_MODE
name = "01.m3u8"
adFile = "bipbop3_1.m3u8"

def displayData(table):
    dao.m_Cursor.execute("SELECT * FROM " + table)
    for row in dao.m_Cursor:
        print row
"""

"""
exists = dao.userExists(user)
print "Pj Exists: " , exists
if not exists:
    print("Initialising user")
    
    dao.insertUserTime(user, time, mode)
    print("Regular Time stamp table:")
    displayData(dbase.RegTimeStamp_TABLE)
    
    dao.insertUserTime(user, 0, dbase.AD_MODE)
    print("Ad Time stamp table:")
    displayData(dbase.AdTimeStamp_TABLE)
    
    dao.setUserMode(user, dbase.REGULAR_MODE)
    print ("User mode")
    displayData(dbase.UserMode_TABLE)
    
    dao.push_Regular(user, name + "-" + str(time))
    print("Regular Stack table:")
    displayData(dbase.RegStack_TABLE)
else:
    currentMode = dao.getUserMode(user)
    print ("Current Mode: "+ currentMode)
    
    if currentMode is dbase.REGULAR_MODE:
        lat = dao.getLastAccessedTime(user, dbase.REGULAR_MODE)
        print ("LAT for Regular mode: " + lat)
        if timepkg.time() - lat >= 20: #20:Ad threshold
            dao.setUserMode(user, dbase.AD_MODE)
            print("user Mode")
            displayData(dbase.UserMode_TABLE)
            
            dao.push_Regular(user, name)
            print("regular stack: ")
            displayData(dbase.RegStack_TABLE)
            
            dao.push_Ad(user, adFile)
            print("ad stack")
            displayData(dbase.AdStack_TABLE)
            
            dao.insertUserTime(user, timepkg.time(), dbase.AD_MODE)
            print("ad time stamp")
            displayData(dbase.AdTimeStamp_TABLE)
"""